IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[csp_rptMetric_PendingSubmissionDetails]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[csp_rptMetric_PendingSubmissionDetails]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- csp_rptMetric_PendingSubmissionDetails 'week', '1/11/2008', 0, '3/15/2009', 0, 0, 0, null, 41359
CREATE  PROCEDURE [dbo].[csp_rptMetric_PendingSubmissionDetails]   
(    
	@PeriodType VARCHAR(50),
	@PeriodStartDate SMALLDATETIME,
	@Institution INT =  0,
	@PeriodEndDate SMALLDATETIME = NULL,
	@Department INT = 0,
	@Unit VARCHAR(1000) = '0',
 	@PrincipalInvestigator VARCHAR(50)='0',
	@Sponsor  VARCHAR(50) = '0',
	@DomainUserId INT
)    
AS    
 
BEGIN 
	--INSIGHT_RPT_DB
	SELECT	ai.Institution, 
			ps.ProposalNumber, 
			ai.PrincipalInvestigator  AS PIName, 
			ai.Department,
			ps.ChiefCode,
			ai.SponsorName AS Sponsor,
			ps.ProposalStatus,
			ps.stat_date,
			ps.LastUpdatedBy,
			ps.DateReceived,
			ps.ProjectStartDate,
			ps.ProjectEndDate,
			ps.DeadlineDate,
			ps.PreawardManager,
			ps.SubmissionDate,
			ps.BudgetAmount,
			ps.InstrumentType
	FROM  rptMetric_PendingSubmissions ps
	INNER JOIN INSIGHT_RPT_DB.dbo.fnGetSecurityAgreements(@DomainUserId) sec
		ON (sec.FolderNumber = ps.ProposalNumber)
	INNER JOIN INSIGHT_RPT_DB.dbo.AgrAgreement_Info ai
		ON ai.FolderNumber = ps.ProposalNumber	
	WHERE ps.PeriodType = @PeriodType
		AND (ai.institutionId = @Institution OR @Institution =0 OR @Institution is NULL)
		AND (ai.DepartmentId = @Department OR @Department =0 OR @Department is NULL)
		AND (ai.UnitId in (Select * from dbo.fnSplitFundNumbers(@Unit)) OR @Unit='0' OR @unit is null or @unit='') 
		AND  ps.PeriodStartDate between @PeriodStartDate and IsNULL(@PeriodEndDate,@PeriodStartDate)
		AND (ai.PrincipalInvestigatorId = @PrincipalInvestigator OR @PrincipalInvestigator IS NULL OR @PrincipalInvestigator = '' OR @PrincipalInvestigator = '0')
		AND (ai.SponsorId = @Sponsor OR @Sponsor IS NULL OR @Sponsor = '' OR @Sponsor = '0')

END

GO

